package com.myeasyexcel.utils;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.metadata.style.WriteFont;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import com.alibaba.fastjson.JSON;
import com.baomidou.mybatisplus.core.toolkit.StringUtils;
import com.myeasyexcel.constant.CustomException;
import com.myeasyexcel.constant.ExcelConst;
import com.myeasyexcel.response.ExcelCode;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.*;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.net.URLEncoder;
import java.util.Arrays;
import java.util.Comparator;
import java.util.List;
import java.util.stream.Collectors;

/**
 * easyexcel工具类
 *
 * @author wangXiaoHu
 * @date 2022/02/09 13:54
 **/
@Slf4j
public class EasyExcelUtilImpl implements IEasyExcelUtil {

    /**
     * 导入excel并导出错误数据
     *
     * @param response 响应体
     * @param file     上传的excel文件
     * @param clazz    导出的实体类class对象
     */
    public void importExcel(HttpServletResponse response, EasyExcelListener<? extends ExcelErrMsg> listener
            , MultipartFile file, Class<? extends ExcelErrMsg> clazz, String fileName) {
        importExcel(response, listener, file, clazz, fileName, 2);
    }

    /**
     * 导入excel并导出错误数据
     *
     * @param response      响应体
     * @param file          上传的excel文件
     * @param clazz         导出的实体类class对象
     * @param headRowNumber 标题所在行号（从1开始）
     */
    @Override
    public void importExcel(HttpServletResponse response, EasyExcelListener<? extends ExcelErrMsg> listener
            , MultipartFile file, Class<? extends ExcelErrMsg> clazz, String fileName, Integer headRowNumber) {
        verifyImportExcelFile(file, clazz);
        // 以下是 处理逻辑
        try {
            long time1 = System.currentTimeMillis();
            listener.setTClass(clazz);
            EasyExcel.read(file.getInputStream(), clazz, listener).sheet()
                    .headRowNumber(headRowNumber).doRead();
            long time2 = System.currentTimeMillis();
            log.info("读取文件耗时{}ms", time2 - time1);
            String s = JSON.toJSONString(listener.getErrList());
            List<? extends ExcelErrMsg> errList = JSON.parseArray(s, clazz);

            // 设置导出文件类型
            String originalFilename = file.getOriginalFilename();
            ExcelTypeEnum excelTypeEnum = null;
            assert originalFilename != null;
            if (originalFilename.endsWith(ExcelTypeEnum.XLSX.getValue())) {
                excelTypeEnum = ExcelTypeEnum.XLSX;
            } else if (originalFilename.endsWith(ExcelTypeEnum.XLS.getValue())) {
                excelTypeEnum = ExcelTypeEnum.XLS;
            }

            exportErrorExcel(response, errList, listener.getSuccessSize(), clazz, fileName, excelTypeEnum);
        } catch (IOException e) {
            e.printStackTrace();
            throw new CustomException(ExcelCode.TEMPLATE_IO_ERROR);
        } catch (NullPointerException ex) {
            log.error("空指针异常", ex);
        }
    }

    /**
     * 导出错误的excel文件
     *
     * @param response    响应体
     * @param errorList   错误的list集合
     * @param successSize 成功的数量
     * @param clazz       导出的实体class对象
     * @param fileName    导出的文件名称
     */
    @Override
    public void exportErrorExcel(HttpServletResponse response, List<? extends ExcelErrMsg> errorList
            , int successSize, Class<? extends ExcelErrMsg> clazz, String fileName, ExcelTypeEnum excelTypeEnum) {
        try {
            long time1 = System.currentTimeMillis();
            int failSize = errorList.size();
            if (successSize == 0 && failSize == 0) {
                response.sendError(201, "模板为空，请添加信息后上传");
            }
            response.setHeader("successSize", String.valueOf(successSize));
            response.setHeader("failSize", String.valueOf(failSize));
            //如果包含错误信息就导出错误信息
            if (!errorList.isEmpty()) {
                WebWriteExcel webWriteExcel = new WebWriteExcel();
                webWriteExcel.webWriteExcel(response, errorList, clazz, fileName, excelTypeEnum);
            }
            long time2 = System.currentTimeMillis();
            log.info("错误数据有{}条，写错误文件耗时{}ms", failSize, time2 - time1);
        } catch (IOException e) {
            e.printStackTrace();
            throw new CustomException(ExcelCode.FILE_WRITE_ERROR);
        }
    }


    /**
     * 校验导入的excel文件标题名称等
     *
     * @param file  excel文件
     * @param clazz excel要导出的实体类class
     */
    private static void verifyImportExcelFile(MultipartFile file, Class<? extends ExcelErrMsg> clazz) {
        verifyFileAndSuffix(file);
    }

    /**
     * 导出excel
     *
     * @param response 响应体
     * @param list     要导出的数据
     * @param clazz    导出的实体类class对象
     * @param fileName 导出的文件名称
     */
    @Override
    public <T> void exportExcel(HttpServletResponse response, List<T> list, Class<T> clazz, String fileName) {
        try {
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            response.setCharacterEncoding("utf-8");
            response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + URLEncoder.encode(fileName, "UTF-8").replaceAll("\\+", "%20") + ".xlsx");

            // 头的策略
            WriteCellStyle headWriteCellStyle = new WriteCellStyle();
            setHeadStyle(headWriteCellStyle);

            // 内容的策略
            WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
            setContentStyle(contentWriteCellStyle);

            // 这个策略是 头是头的样式 内容是内容的样式 其他的策略可以自己实现
            HorizontalCellStyleStrategy horizontalCellStyleStrategy =
                    new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);

            // 这里 需要指定写用哪个class去写，然后写到第一个sheet，名字为模板 然后文件流会自动关闭
            EasyExcel.write(response.getOutputStream(), clazz)
                    .registerWriteHandler(horizontalCellStyleStrategy)
                    .registerWriteHandler(new MatchColumnWidthStyleStrategy())
                    .sheet(fileName).doWrite(list);
        } catch (Exception e) {
            log.error("导出excel信息错误：", e);
        }
    }

    /**
     * 设置excel导出头样式
     *
     * @param headWriteCellStyle
     */
    private static void setHeadStyle(WriteCellStyle headWriteCellStyle) {
        // 背景设置为指定颜色
        headWriteCellStyle.setFillForegroundColor(IndexedColors.ROYAL_BLUE.getIndex());
        // 设置头字体
        WriteFont headWriteFont = new WriteFont();
        headWriteFont.setFontName("宋体");
        headWriteFont.setFontHeightInPoints((short) 11);
        headWriteFont.setBold(false);
        headWriteFont.setColor((short) 9);
        headWriteCellStyle.setWriteFont(headWriteFont);
        headWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
    }

    /**
     * 设置excel 导出内容样式
     *
     * @param contentWriteCellStyle
     */
    private static void setContentStyle(WriteCellStyle contentWriteCellStyle) {
        WriteFont contentWriteFont = new WriteFont();
        // 字体 样式 大小
        contentWriteFont.setFontName("宋体");
        contentWriteFont.setFontHeightInPoints((short) 11);
        contentWriteCellStyle.setWriteFont(contentWriteFont);
        contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
    }


    /**
     * 校验文件大小 和 文件后缀名
     *
     * @param file excel文件
     */
    private static void verifyFileAndSuffix(MultipartFile file) {
        // 对file进行判空和模板格式校验
        if (file == null || file.getSize() == 0) {
            throw new CustomException(ExcelCode.TEMPLATE_ZERO_BYTES);
        }
        String originalFileName = file.getOriginalFilename();
        if (StringUtils.isEmpty(originalFileName)) {
            throw new CustomException(ExcelCode.GET_FILE_NAME_ERROR);
        }

        // 校验文件后缀 是否是xlsx或者xls
        if (!(originalFileName.endsWith(ExcelTypeEnum.XLSX.getValue()) || originalFileName.endsWith(ExcelTypeEnum.XLS.getValue()))) {
            throw new CustomException(ExcelCode.FILE_ERROR);
        }
    }

    /**
     * 校验员工模板的标题行内容是否正确
     *
     * @param file
     */
    private static int verifyTemplateColName(MultipartFile file, Class<? extends ExcelErrMsg> clazz) {
        InputStream inputStream = null;
        int headRow = 0;
        try {
            inputStream = file.getInputStream();
            Workbook workbook = WorkbookFactory.create(inputStream);
            Sheet sheet = workbook.getSheetAt(0);
            // 校验文件名称
            headRow = doVerifyColName(sheet, clazz);
        } catch (IOException e) {
            if (inputStream != null) {
                try {
                    inputStream.close();
                } catch (IOException ex) {
                    log.error("关闭inputStream文件流异常", ex);
                }
            }
            log.error("读取文件异常", e);
        }
        return headRow;
    }

    /**
     * 校验列名称，获取sheet的列标题所在行数
     *
     * @param sheet 工作表
     * @param clazz 实体类对象
     * @return 列标题所在的逻辑行数
     */
    public static int doVerifyColName(Sheet sheet, Class<? extends ExcelErrMsg> clazz) {
        int curRow = 0;
        Field[] declaredFields = clazz.getDeclaredFields();
        List<ExcelProperty> list = Arrays.stream(declaredFields)
                .filter(field -> field.getAnnotation(ExcelProperty.class) != null)
                .map(field -> field.getAnnotation(ExcelProperty.class))
                .sorted(Comparator.comparing(ExcelProperty::index)).collect(Collectors.toList());
        String firstColName = list.get(0).value()[0];
        Row row = sheet.getRow(curRow);
        while (curRow <= sheet.getLastRowNum()) {
            row = sheet.getRow(curRow);
            if (row != null && row.getCell(0) != null && firstColName.equals(row.getCell(0).getStringCellValue())) {
                break;
            }
            curRow++;
        }
        if (curRow > sheet.getLastRowNum()) {
            throw new CustomException(ExcelCode.TEMPLATE_ROW_NAME_ERROR);
        }
        for (int i = 1; i < list.size(); i++) {
            if (ExcelConst.ERRMSG.equals(list.get(i).value()[0])) {
                continue;
            }
            if (row != null && !list.get(i).value()[0].equals(row.getCell(i).getStringCellValue())) {
                throw new CustomException(ExcelCode.TEMPLATE_ROW_NAME_ERROR);
            }
        }
        return curRow;
    }

    /**
     * 验证列标题名称
     *
     * @param sheet    sheet
     * @param clazz    导出的实体类对象
     * @param rowIndex 列名称所在第几行
     */
    private static void verifyColName(Sheet sheet, Class clazz, int rowIndex) {
        Row row1 = sheet.getRow(rowIndex);
        if (row1 == null) {
            throw new CustomException(ExcelCode.TEMPLATE_ROW_NAME_ERROR);
        }
        Field[] declaredFields = clazz.getDeclaredFields();
        List<ExcelProperty> list = Arrays.stream(declaredFields)
                .filter(field -> field.getAnnotation(ExcelProperty.class) != null)
                .map(field -> field.getAnnotation(ExcelProperty.class))
                .sorted(Comparator.comparing(ExcelProperty::index)).collect(Collectors.toList());
        for (int i = 0; i < list.size(); i++) {
            if (ExcelConst.ERRMSG.equals(list.get(i).value()[0])) {
                continue;
            }
            if (!list.get(i).value()[0].equals(row1.getCell(i).getStringCellValue())) {
                throw new CustomException(ExcelCode.TEMPLATE_ROW_NAME_ERROR);
            }
        }
    }
}
